CREATE EXTERNAL TABLE jms_dm.dm_end_dispatch_third_loss_detail_dt(
  waybill_no string COMMENT '运单号',
  taking_time timestamp COMMENT '揽收时间',
  collect_in_time timestamp COMMENT '入库时间',
  create_time timestamp COMMENT '申报时间',
  closing_time timestamp COMMENT '结案时间',
  exception_desc string COMMENT '异常说明',
  res_network_code1 string COMMENT '责任网点code1',
  res_network_name1 string COMMENT '责任网点name1',
  res_agent_code1 string COMMENT '责任网点所属代理区code1',
  res_agent_name1 string COMMENT '责任网点所属代理区name1',
  res_fran_code1 string COMMENT '责任网点1所属加盟商code',
  res_fran_name1 string COMMENT '责任网点1所属加盟商name',
  res_virt_code1 string COMMENT '责任网点1所属虚拟代理区code',
  res_virt_name1 string COMMENT '责任网点1所属虚拟代理区name',
  res_network_code2 string COMMENT '责任网点code1',
  res_network_name2 string COMMENT '责任网点name1',
  res_agent_code2 string COMMENT '责任网点所属代理区code2',
  res_agent_name2 string COMMENT '责任网点所属代理区name2',
  res_fran_code2 string COMMENT '责任网点2所属加盟商code',
  res_fran_name2 string COMMENT '责任网点2所属加盟商name',
  res_virt_code2 string COMMENT '责任网点2所属虚拟代理区code',
  res_virt_name2 string COMMENT '责任网点2所属虚拟代理区name',
  res_network_code3 string COMMENT '责任网点code3',
  res_network_name3 string COMMENT '责任网点name3',
  res_agent_code3 string COMMENT '责任网点所属代理区code3',
  res_agent_name3 string COMMENT '责任网点所属代理区name3',
  res_fran_code3 string COMMENT '责任网点3所属加盟商code',
  res_fran_name3 string COMMENT '责任网点3所属加盟商name',
  res_virt_code3 string COMMENT '责任网点3所属虚拟代理区code',
  res_virt_name3 string COMMENT '责任网点3所属虚拟代理区name',
  res_network_code4 string COMMENT '责任网点code4',
  res_network_name4 string COMMENT '责任网点name4',
  res_agent_code4 string COMMENT '责任网点所属代理区code4',
  res_agent_name4 string COMMENT '责任网点所属代理区name4',
  res_fran_code4 string COMMENT '责任网点4所属加盟商code',
  res_fran_name4 string COMMENT '责任网点4所属加盟商name',
  res_virt_code4 string COMMENT '责任网点4所属虚拟代理区code',
  res_virt_name4 string COMMENT '责任网点4所属虚拟代理区name',
  res_network_code5 string COMMENT '责任网点code5',
  res_network_name5 string COMMENT '责任网点name5',
  res_agent_code5 string COMMENT '责任网点所属代理区code5',
  res_agent_name5 string COMMENT '责任网点所属代理区name5',
  res_fran_code5 string COMMENT '责任网点5所属加盟商code',
  res_fran_name5 string COMMENT '责任网点5所属加盟商name',
  res_virt_code5 string COMMENT '责任网点5所属虚拟代理区code',
  res_virt_name5 string COMMENT '责任网点5所属虚拟代理区name',
  award_amount_total decimal(22,2) COMMENT '合计裁定金额',
  store_code string COMMENT '门店code',
  store_name string COMMENT '门店名称',
  business_id string COMMENT '门店品牌code',
  business_name string COMMENT '门店名称',
  date_time date COMMENT 'doris分区字段',
  res_network_amount1 decimal(22,2) COMMENT '责任网点1赔付金额',
  res_network_amount2 decimal(22,2) COMMENT '责任网点2赔付金额',
  res_network_amount3 decimal(22,2) COMMENT '责任网点3赔付金额',
  res_network_amount4 decimal(22,2) COMMENT '责任网点4赔付金额',
  res_network_amount5 decimal(22,2) COMMENT '责任网点5赔付金额',
  network_count int COMMENT '责任网点数')
COMMENT '末端监控-三方遗失明细'
PARTITIONED BY (
  dt string COMMENT '时间分区(yyyy-MM-dd)')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_end_dispatch_third_loss_cnt_dt'
TBLPROPERTIES ( 'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');
-----------------------------------------------------------------------------
CREATE EXTERNAL TABLE jms_dm.dm_end_dispatch_third_loss_cnt_dt(
  closing_time date COMMENT '结案时间', 
  res_agent_code string COMMENT '责任网点所属代理区code', 
  res_agent_name string COMMENT '责任网点所属代理区name', 
  res_fran_code string COMMENT '责任网点所属加盟商code', 
  res_fran_name string COMMENT '责任网点所属加盟商name', 
  res_virt_code string COMMENT '责任网点所属虚拟代理区code', 
  res_virt_name string COMMENT '责任网点所属虚拟代理区code', 
  res_provider_id string COMMENT '责任网点所属省id', 
  res_provider_desc string COMMENT '责任网点所属省name', 
  res_city_id string COMMENT '责任网点所属市id', 
  res_city_desc string COMMENT '责任网点所属市name', 
  res_zone_code string COMMENT '片区code', 
  res_zone_name string COMMENT '片区name', 
  res_network_code string COMMENT '责任网点code', 
  res_network_name string COMMENT '责任网点name', 
  store_code string COMMENT '门店code', 
  store_name string COMMENT '门店名称', 
  business_id string COMMENT '门店品牌code', 
  business_name string COMMENT '门店品牌名称', 
  third_loss_cnt decimal(22,2) COMMENT '三方遗失票数', 
  collect_in_cnt int COMMENT '入库量', 
  date_time date COMMENT 'doris分区字段')
COMMENT '末端监控-三方遗失汇总'
PARTITIONED BY ( 
  dt string COMMENT '时间分区(yyyy-MM-dd)')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_end_dispatch_third_loss_cnt_dt'
TBLPROPERTIES ( 'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');

alter table jms_dm.dm_end_dispatch_third_loss_cnt_dt add columns(source string '驿站类型 机柜、驿站')cascade ;